package com.vortex.cloud.zhsw.xinyang.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.fastjson.JSON;
import com.github.liaochong.myexcel.core.DefaultStreamExcelBuilder;
import com.github.liaochong.myexcel.core.WorkbookType;
import com.github.liaochong.myexcel.core.constant.ImageFile;
import com.github.liaochong.myexcel.core.container.Pair;
import com.github.liaochong.myexcel.core.converter.WriteConverterContext;
import com.github.liaochong.myexcel.core.strategy.WidthStrategy;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.vortex.cloud.sdk.api.config.VortexUrlConfig;
import com.vortex.cloud.sdk.api.dto.jcss.reborn.ExtendFieldDTO;
import com.vortex.cloud.sdk.api.dto.jcss.reborn.FacilityTypeDTO;
import com.vortex.cloud.sdk.api.service.IJcssService;
import com.vortex.cloud.vfs.cmmon.web.component.RestTemplateComponent;
import com.vortex.cloud.vfs.lite.base.dto.FileDTO;
import com.vortex.cloud.vfs.lite.base.dto.GeometryDTO;
import com.vortex.cloud.zhsw.xinyang.annotation.ImportAttachField;
import com.vortex.cloud.zhsw.xinyang.converter.DictDataWriterConverter;
import com.vortex.cloud.zhsw.xinyang.converter.GeometryWriteConverter;
import com.vortex.cloud.zhsw.xinyang.dto.DictDataDTO;
import com.vortex.cloud.zhsw.xinyang.dto.excel.ExportExcelColumnDTO;
import com.vortex.cloud.zhsw.xinyang.service.ExportService;
import com.vortex.cloud.zhsw.xinyang.support.Constants;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.springframework.cglib.beans.BeanMap;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;

import javax.annotation.Resource;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 导出Service
 *
 * @author huohao@vortexinfo.cn
 * @date 2022/3/22 10:00
 */
@Slf4j
@Service
public class ExportServiceImpl implements ExportService {
    private final RestTemplateComponent restTemplateComponent;
    private final VortexUrlConfig vortexUrlConfig;
    /**
     * 附件服务返回的body内容比较特殊
     */
    private static final String DOWNLOAD_FILE_THUMBNAIL_ERROR = "success:false";

    @Resource
    private IJcssService jcssService;

    public ExportServiceImpl(RestTemplateComponent restTemplateComponent, VortexUrlConfig vortexUrlConfig) {
        this.restTemplateComponent = restTemplateComponent;
        this.vortexUrlConfig = vortexUrlConfig;
    }

    /**
     * 导出Excel
     *
     * @param fileName   导出文件名称
     * @param extension  文件类型
     * @param columnJson 导出字段
     * @param records    记录
     * @return org.springframework.http.ResponseEntity<byte [ ]>
     * @author huohao@vortexinfo.cn
     */
    @Override
    public <T> ResponseEntity<byte[]> exportExcel(String fileName, String extension, String columnJson, List<T> records, HashMap<Integer, String[]> downMap, Integer exportType) {
        // 经纬度导出
        WriteConverterContext.registering(new GeometryWriteConverter());
        WriteConverterContext.registering(new DictDataWriterConverter());
        try {
            List<ExportExcelColumnDTO> columns = JSON.parseArray(columnJson, ExportExcelColumnDTO.class);
            Assert.notEmpty(columns, "数据列不能为空");
            List<String> titles = Lists.newArrayList();
            List<String> orders = Lists.newArrayList();
            Map<String, String> formatMapByField = Maps.newHashMap();
            String seqNumFieldName = "seqNum" + System.currentTimeMillis();
            titles.add("序号");
            orders.add(seqNumFieldName);
            for (ExportExcelColumnDTO column : columns) {
                String title = column.getTitle();
                String field = column.getField();
                String format = column.getFormat();
                Assert.hasText(title, "数据列标题不能为空");
                Assert.hasText(field, "数据列字段不能为空");
                titles.add(title);
                orders.add(field);
                formatMapByField.put(field, format);
            }
            // 图片字段名称
            List<String> attachFieldList = Lists.newArrayList();
            // 获取图片字段
            if (CollectionUtils.isNotEmpty(records)) {
                for (Field field : records.get(0).getClass().getDeclaredFields()) {
                    if (field.isAnnotationPresent(ImportAttachField.class)) {
                        attachFieldList.add(field.getName());
                    }
                }
            }
            List<Map> results = null;
            if (CollectionUtils.isNotEmpty(records)) {
                results = records.stream().map(a -> {
                    Map map = Maps.newHashMap();
                    BeanMap beanMap = BeanMap.create(a);
                    for (Object key : beanMap.keySet()) {
                        Object value = beanMap.get(key);
                        map.put(key + "", value);
                        if (key.equals("dataJson")) {
                            JSONObject jsonObject = JSONUtil.parseObj(value);
                            if (CollUtil.isNotEmpty(jsonObject)) {
                                for (Object jsonKey : jsonObject.keySet()) {
                                    map.put(jsonKey + "", jsonObject.get(jsonKey));
                                }
                            }
                            map.remove("dataJson");
                        }
                    }
                    return map;
                }).collect(Collectors.toList());
                getExportList(formatMapByField, seqNumFieldName, attachFieldList, results);
            }
            DefaultStreamExcelBuilder<Map> streamExcelBuilder = DefaultStreamExcelBuilder
                    .of(Map.class).sheetName("数据")
                    .widthStrategy(WidthStrategy.COMPUTE_AUTO_WIDTH).titles(titles)
                    .style("title->text-align:center", "cell->text-align:center;", "cell->border-style:thin")
                    .fieldDisplayOrder(orders).workbookType(WorkbookType.valueOf(extension.toUpperCase()))
                    .start();
            streamExcelBuilder.append(results);
            Workbook workbook = streamExcelBuilder.build();
            setRedTitle(columns, workbook, 0);
            if (CollUtil.isNotEmpty(downMap)) {
                selectList(workbook, downMap, 0);
            }
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
//            getExtraWorkbook(exportType, columns, titles, workbook, byteArrayOutputStream,downMap,null,null);
            workbook.write(byteArrayOutputStream);
            byte[] bytes = byteArrayOutputStream.toByteArray();
            byteArrayOutputStream.flush();
            byteArrayOutputStream.close();
            workbook.close();
            streamExcelBuilder.close();
            fileName = fileName + "." + extension;
            fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
            fileName = new String(fileName.getBytes(), StandardCharsets.ISO_8859_1.name());
            HttpHeaders httpHeaders = new HttpHeaders();
            httpHeaders.add(HttpHeaders.CONTENT_TYPE, MediaType.APPLICATION_JSON_UTF8_VALUE);
            httpHeaders.add(HttpHeaders.ACCEPT_CHARSET, StandardCharsets.UTF_8.name());
            httpHeaders.add(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename*=utf-8'zh_cn'" + fileName);
            return ResponseEntity.ok()
                    .headers(httpHeaders)
                    .body(bytes);
        } catch (Exception e) {
            log.error("导出失败", e);
            throw new RuntimeException(e);
        }
    }

    @Override
    public <T> ResponseEntity<byte[]> exportExcel(String fileName, String extension, String columnJson, List<T> records, HashMap<String, String[]> downMap, Integer exportType, String tenantId, String facilityType) {
        // 经纬度导出
        WriteConverterContext.registering(new GeometryWriteConverter());
        WriteConverterContext.registering(new DictDataWriterConverter());
        try {
            List<ExportExcelColumnDTO> columns = JSON.parseArray(columnJson, ExportExcelColumnDTO.class);
            Assert.notEmpty(columns, "数据列不能为空");
            List<String> titles = Lists.newArrayList();
            List<String> orders = Lists.newArrayList();
            Map<String, String> formatMapByField = Maps.newHashMap();
            String seqNumFieldName = "seqNum" + System.currentTimeMillis();
            titles.add("序号");
            orders.add(seqNumFieldName);
            for (ExportExcelColumnDTO column : columns) {
                String title = column.getTitle();
                String field = column.getField();
                String format = column.getFormat();
                Assert.hasText(title, "数据列标题不能为空");
                Assert.hasText(field, "数据列字段不能为空");
                titles.add(title);
                orders.add(field);
                formatMapByField.put(field, format);
            }
            // 图片字段名称
            List<String> attachFieldList = Lists.newArrayList();
            // 获取图片字段
            if (CollectionUtils.isNotEmpty(records)) {
                for (Field field : records.get(0).getClass().getDeclaredFields()) {
                    if (field.isAnnotationPresent(ImportAttachField.class)) {
                        attachFieldList.add(field.getName());
                    }
                }
            }
            List<Map> results = null;
            if (CollectionUtils.isNotEmpty(records)) {
                results = records.stream().map(a -> {
                    Map map = Maps.newHashMap();
                    BeanMap beanMap = BeanMap.create(a);
                    for (Object key : beanMap.keySet()) {
                        Object value = beanMap.get(key);
                        map.put(key + "", value);
                        if (key.equals("dataJson")) {
                            JSONObject jsonObject = JSONUtil.parseObj(value);
                            if (CollUtil.isNotEmpty(jsonObject)) {
                                for (Object jsonKey : jsonObject.keySet()) {
                                    map.put(jsonKey + "", jsonObject.get(jsonKey));
                                }
                            }
                            map.remove("dataJson");
                        }
                    }
                    return map;
                }).collect(Collectors.toList());
                getExportList(formatMapByField, seqNumFieldName, attachFieldList, results);
            }
            DefaultStreamExcelBuilder<Map> streamExcelBuilder = DefaultStreamExcelBuilder
                    .of(Map.class).sheetName("数据")
                    .widthStrategy(WidthStrategy.COMPUTE_AUTO_WIDTH).titles(titles)
                    .style("title->text-align:center", "cell->text-align:center;", "cell->border-style:thin")
                    .fieldDisplayOrder(orders).workbookType(WorkbookType.valueOf(extension.toUpperCase()))
                    .start();
            streamExcelBuilder.append(results);
            Workbook workbook = streamExcelBuilder.build();
            setRedTitle(columns, workbook, 0);
            if (CollUtil.isNotEmpty(downMap)) {
                setExcelByCode(workbook, downMap, 0, columns);
            }
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            getExtraWorkbook(exportType, columns, titles, workbook, byteArrayOutputStream, downMap, tenantId, facilityType);
            workbook.write(byteArrayOutputStream);
            byte[] bytes = byteArrayOutputStream.toByteArray();
            byteArrayOutputStream.flush();
            byteArrayOutputStream.close();
            workbook.close();
            streamExcelBuilder.close();
            fileName = fileName + "." + extension;
            fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
            fileName = new String(fileName.getBytes(), StandardCharsets.ISO_8859_1.name());
            HttpHeaders httpHeaders = new HttpHeaders();
            httpHeaders.add(HttpHeaders.CONTENT_TYPE, MediaType.APPLICATION_JSON_UTF8_VALUE);
            httpHeaders.add(HttpHeaders.ACCEPT_CHARSET, StandardCharsets.UTF_8.name());
            httpHeaders.add(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename*=utf-8'zh_cn'" + fileName);
            return ResponseEntity.ok()
                    .headers(httpHeaders)
                    .body(bytes);
        } catch (Exception e) {
            log.error("导出失败", e);
            throw new RuntimeException(e);
        }
    }

    @Override
    public <T> ResponseEntity<byte[]> exportExcelDynamics(String fileName, String extension, String columnJson, List<T> records, HashMap<Integer, String[]> downMap, List<Map<String, String>> extandData, Boolean isMergeFlowAnalyse) {
        // 经纬度导出
        WriteConverterContext.registering(new GeometryWriteConverter());
        WriteConverterContext.registering(new DictDataWriterConverter());
        try {
            List<ExportExcelColumnDTO> columns = JSON.parseArray(columnJson, ExportExcelColumnDTO.class);
            Assert.notEmpty(columns, "数据列不能为空");
            List<String> titles = Lists.newArrayList();
            List<String> orders = Lists.newArrayList();
            Map<String, String> formatMapByField = Maps.newHashMap();
            String seqNumFieldName = "seqNum" + System.currentTimeMillis();
            titles.add("序号");
            orders.add(seqNumFieldName);
            for (ExportExcelColumnDTO column : columns) {
                String title = column.getTitle();
                String field = column.getField();
                String format = column.getFormat();
                Assert.hasText(title, "数据列标题不能为空");
                Assert.hasText(field, "数据列字段不能为空");
                titles.add(title);
                orders.add(field);
                formatMapByField.put(field, format);
            }
            // 图片字段名称
            List<String> attachFieldList = Lists.newArrayList();
            // 获取图片字段
            if (CollectionUtils.isNotEmpty(records)) {
                for (Field field : records.get(0).getClass().getDeclaredFields()) {
                    if (field.isAnnotationPresent(com.vortex.cloud.zhsw.jcyj.annotation.ImportAttachField.class)) {
                        attachFieldList.add(field.getName());
                    }
                }
            }
            List<Map> results = null;
            if (CollectionUtils.isNotEmpty(records)) {
                results = records.stream().map(a -> {
                    Map map = Maps.newHashMap();
                    BeanMap beanMap = BeanMap.create(a);
                    for (Object key : beanMap.keySet()) {
                        Object value = beanMap.get(key);
                        map.put(key + "", value);
                    }
                    return map;
                }).collect(Collectors.toList());
                getExportList(formatMapByField, seqNumFieldName, attachFieldList, results, extandData);
            }
            DefaultStreamExcelBuilder<Map> streamExcelBuilder = DefaultStreamExcelBuilder
                    .of(Map.class)
                    .widthStrategy(WidthStrategy.COMPUTE_AUTO_WIDTH)
                    .titles(titles)
                    .style("title->text-align:center", "cell->text-align:center;")
                    .fieldDisplayOrder(orders).workbookType(WorkbookType.valueOf(extension.toUpperCase()))
                    .start();
            streamExcelBuilder.append(results);
            Workbook workbook = streamExcelBuilder.build();
            setRedTitle(columns, workbook, 0);
            if (CollUtil.isNotEmpty(downMap)) {
                selectList(workbook, downMap, 0);
            }
            if (isMergeFlowAnalyse) {
                mergeDataFlowAnalyse(workbook);
            }
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            workbook.write(byteArrayOutputStream);
            byte[] bytes = byteArrayOutputStream.toByteArray();
            byteArrayOutputStream.flush();
            byteArrayOutputStream.close();
            workbook.close();
            streamExcelBuilder.close();
            fileName = fileName + "." + extension;
            fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
            fileName = new String(fileName.getBytes(), StandardCharsets.ISO_8859_1.name());
            HttpHeaders httpHeaders = new HttpHeaders();
            httpHeaders.add(HttpHeaders.CONTENT_TYPE, MediaType.APPLICATION_JSON_UTF8_VALUE);
            httpHeaders.add(HttpHeaders.ACCEPT_CHARSET, StandardCharsets.UTF_8.name());
            httpHeaders.add(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename*=utf-8'zh_cn'" + fileName);
            return ResponseEntity.ok()
                    .headers(httpHeaders)
                    .body(bytes);
        } catch (Exception e) {
            log.error("导出失败", e);
            throw new RuntimeException(e);
        }
    }

    private void mergeDataFlowAnalyse(Workbook workbook) {
        Sheet sheet = workbook.getSheetAt(0);
        CellStyle cellStyle = workbook.getCellStyleAt(0);
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中
        for (int index = 1; index <= sheet.getLastRowNum(); index++) {
            Row row = sheet.getRow(index);
            for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                if (row.getCell(cellIndex).getCellStyle() != null) {
                    row.getCell(cellIndex).getCellStyle().setAlignment(HorizontalAlignment.CENTER);
                    row.getCell(cellIndex).getCellStyle().setVerticalAlignment(VerticalAlignment.CENTER);
                } else {
                    row.getCell(cellIndex).setCellStyle(cellStyle);
                }
            }
        }

        Map<String, String> exist = new HashMap<>(16);
        exist.put("manageUnitName", sheet.getRow(1).getCell(1).getStringCellValue());
        exist.put("facilityName", sheet.getRow(1).getCell(2).getStringCellValue());
        exist.put("startRow", "1");
        Boolean mergeFlag = false;
        Boolean endFlag = true;
        // 合并第2列
        for (int index = 2; index < sheet.getLastRowNum(); index++) {
            Row row = sheet.getRow(index);
            String manageUnitName = row.getCell(1).getStringCellValue();

            if (exist.get("manageUnitName").equals(manageUnitName)) {
                mergeFlag = true;
                endFlag = false;
            } else {
                exist.put("manageUnitName", manageUnitName);
                if (mergeFlag && !endFlag) {
                    endFlag = true;
                } else {
                    mergeFlag = false;
                }
                if (!mergeFlag) {
                    exist.put("startRow", String.valueOf(index));
                }
            }
            if (mergeFlag && endFlag) {
                sheet.addMergedRegion(new CellRangeAddress(Integer.parseInt(exist.get("startRow")), index - 1, 1, 1));
                mergeFlag = false;
                exist.put("manageUnitName", sheet.getRow(index).getCell(1).getStringCellValue());
                exist.put("startRow", String.valueOf(index));
            }
        }

        // 合并第三列、倒数第三列、倒数第二列、倒数第一列
        mergeFlag = false;
        endFlag = true;
        exist.put("startRow", "1");
        for (int index = 2; index < sheet.getLastRowNum(); index++) {
            Row row = sheet.getRow(index);
            String facilityName = row.getCell(2).getStringCellValue();

            if (exist.get("facilityName").equals(facilityName)) {
                mergeFlag = true;
                endFlag = false;
            } else {
                exist.put("facilityName", facilityName);
                if (mergeFlag && !endFlag) {
                    endFlag = true;
                } else {
                    mergeFlag = false;
                }
                if (!mergeFlag) {
                    exist.put("startRow", String.valueOf(index));
                }
            }
            if (mergeFlag && endFlag) {
                sheet.addMergedRegion(new CellRangeAddress(Integer.parseInt(exist.get("startRow")), index - 1, 2, 2));
                sheet.addMergedRegion(new CellRangeAddress(Integer.parseInt(exist.get("startRow")), index - 1, sheet.getRow(0).getLastCellNum() - 3, sheet.getRow(0).getLastCellNum() - 3));
                sheet.addMergedRegion(new CellRangeAddress(Integer.parseInt(exist.get("startRow")), index - 1, sheet.getRow(0).getLastCellNum() - 2, sheet.getRow(0).getLastCellNum() - 2));
                sheet.addMergedRegion(new CellRangeAddress(Integer.parseInt(exist.get("startRow")), index - 1, sheet.getRow(0).getLastCellNum() - 1, sheet.getRow(0).getLastCellNum() - 1));
                mergeFlag = false;
                exist.put("facilityName", sheet.getRow(index).getCell(2).getStringCellValue());
                exist.put("startRow", String.valueOf(index));
            }
        }
    }

    private void getExtraWorkbook(Integer exportType, List<ExportExcelColumnDTO> columns, List<String> titles,
                                  Workbook workbook, ByteArrayOutputStream byteArrayOutputStream,
                                  HashMap<String, String[]> downMap, String tenantId, String facilityType) throws IOException {
        int t = 0;
        if (Objects.nonNull(downMap)) {
            t = getMap(downMap, columns).size();
        }
        if (exportType != null && exportType == Constants.Figure.ONE) {
            Workbook workbook1 = DefaultStreamExcelBuilder.of(Map.class, workbook)
                    .sheetName("样例数据")
                    .titles(titles)
                    .widthStrategy(WidthStrategy.COMPUTE_AUTO_WIDTH)
                    .style("title->text-align:center", "cell->text-align:center;", "cell->border-style:thin")
                    .start().build();
            setRedTitle(columns, workbook1, t + 1);
            if (CollUtil.isNotEmpty(downMap)) {
                setExcelByCode(workbook, downMap, t + 1, columns);
            }
            setTemplate(workbook1, t + 1, columns, tenantId, facilityType);
            workbook1.write(byteArrayOutputStream);
            workbook1.close();
        }
    }

    private static int writeTip(ExcelWriter excelWriter, int rowNum, Font font, String content) {
        excelWriter.writeCellValue(0, rowNum, content);
        CellStyle cellStyle = excelWriter.createCellStyle(0, rowNum);
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        cellStyle.setFont(font);
        return ++rowNum;
    }

    private void setTemplate(Workbook workbook, Integer size, List<ExportExcelColumnDTO> columns, String tenantId, String facilityType) {
        Sheet sheet = workbook.getSheetAt(size);
        if (StrUtil.isNotBlank(facilityType)) {
            FacilityTypeDTO f = jcssService.getFacilityTypeByCode(tenantId, facilityType);
            if (Objects.nonNull(f) && CollUtil.isNotEmpty(f.getFieldJson())) {
                List<ExtendFieldDTO> dtoList = f.getFieldJson().stream().filter(ExtendFieldDTO::getEnabled).filter(a -> Objects.nonNull(a.getImportSampleData())).collect(Collectors.toList());
                Map<String, String> map = dtoList.stream().collect(Collectors.toMap(ExtendFieldDTO::getShowKey, ExtendFieldDTO::getImportSampleData, (v1, v2) -> v2));
                Row row = sheet.createRow(1);
                Cell cell0 = row.createCell(0);
                cell0.setCellValue(1);
                for (int i = 0; i < columns.size(); i++) {
                    String code = columns.get(i).getField();
                    if (Objects.nonNull(code) && map.containsKey(code)) {
                        Cell cell = row.createCell(i + 1);
                        cell.setCellValue(map.get(code));
                    }
                }
            }
        }
        // 创建普通单元格样式
        CellStyle style = workbook.createCellStyle();
        Font blackAndBoldFont = workbook.createFont();
        blackAndBoldFont.setBold(true);
        style.setFont(blackAndBoldFont);
        // 创建必填单元格样式
        CellStyle requiredStyle = workbook.createCellStyle();
        // 设置必填红色
        Font redAndBoldFont = workbook.createFont();
        redAndBoldFont.setColor(Font.COLOR_RED);
        redAndBoldFont.setBold(true);
        requiredStyle.setFont(redAndBoldFont);
        int rowNum = 5;
        rowNum = writeTip(sheet, rowNum, requiredStyle, "本表仅作为样例参考，请在“导入数据”页签填写数据");
        rowNum = writeTip(sheet, rowNum, style, "注1：逗号为英文逗号");
        rowNum = writeTip(sheet, rowNum, style, "注2：红色字段为必填字段，日期格式要求yyyy-MM-dd");
        rowNum = writeTip(sheet, rowNum, style, "注3：导入时不要清除注释，直接删除注释行");
        rowNum = writeTip(sheet, rowNum, style, "注4：导入数据量大时，请勿频繁操作上传按钮");
        writeTip(sheet, rowNum, style, "注5：如果有设施位置，格式为:经度,纬度;经度,纬度(如120.13430728793021,31.889294979558503;120.1343611798782,31.890452339916106),经纬度类型为租户配置类型");

    }

    private static int writeTip(Sheet sheet, int rowNum, CellStyle style, String content) {
        Row titleRow = sheet.createRow(rowNum);
        Cell seqNoTitleCell = titleRow.createCell(0);
        seqNoTitleCell.setCellValue(content);
        seqNoTitleCell.setCellStyle(style);
        return ++rowNum;
    }

    private void setRedTitle(List<ExportExcelColumnDTO> columns, Workbook workbook, Integer sheetNum) {
        Sheet sheet = workbook.getSheetAt(sheetNum);
        // 创建普通单元格样式
        CellStyle style = workbook.createCellStyle();
        // 居中格式
        style.setAlignment(HorizontalAlignment.CENTER);

        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());


        // 创建必填单元格样式
        CellStyle requiredStyle = workbook.createCellStyle();
        // 居中格式
        requiredStyle.setAlignment(HorizontalAlignment.CENTER);

        requiredStyle.setBorderBottom(BorderStyle.THIN);
        requiredStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        requiredStyle.setBorderLeft(BorderStyle.THIN);
        requiredStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        requiredStyle.setBorderRight(BorderStyle.THIN);
        requiredStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        requiredStyle.setBorderTop(BorderStyle.THIN);
        requiredStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());

        // 设置必填红色
        Font font = workbook.createFont();
        font.setColor(Font.COLOR_RED);
        requiredStyle.setFont(font);
        // 表头
        Row titleRow = sheet.createRow(0);
        // 表头-序号
        Cell seqNoTitleCell = titleRow.createCell(0);
        seqNoTitleCell.setCellValue("序号");
        seqNoTitleCell.setCellStyle(style);
        for (int i = 0; i < columns.size(); i++) {
            Cell cell = titleRow.createCell(i + 1);
            cell.setCellValue(columns.get(i).getTitle());
            if (null != columns.get(i).getRequired() && columns.get(i).getRequired()) {
                cell.setCellStyle(requiredStyle);
            } else {
                cell.setCellStyle(style);
            }
        }
    }

    private void getExportList(Map<String, String> formatMapByField, String seqNumFieldName, List<String> attachFieldList, List<Map> results) {
        int seqNum = 1;
        for (Map<String, Object> result : results) {
            result.put(seqNumFieldName, seqNum++);
            for (Map.Entry<String, Object> entry : result.entrySet()) {
                String field = entry.getKey();
                Object value = entry.getValue();
                String format = formatMapByField.get(field);

                if (Objects.isNull(value)) {
                    continue;
                }
                setEntryValue(entry, value, format);
                if (CollectionUtils.isNotEmpty(attachFieldList) && attachFieldList.contains(field)) {
                    setImageValue(entry, value);
                }
            }
        }
    }

    private void getExportList(Map<String, String> formatMapByField, String seqNumFieldName, List<String> attachFieldList, List<Map> results, List<Map<String, String>> extandData) {
        int seqNum = 1;
        int index = 0;
        for (Map<String, Object> result : results) {
            result.put(seqNumFieldName, seqNum++);
            if (CollUtil.isEmpty(extandData)) {
                continue;
            }
            Map<String, String> data = extandData.get(index);
            index++;
            for (Map.Entry<String, String> entry : data.entrySet()) {
                result.put(entry.getKey(), entry.getValue());
            }
            for (Map.Entry<String, Object> entry : result.entrySet()) {
                String field = entry.getKey();
                Object value = entry.getValue();
                String format = formatMapByField.get(field);

                if (Objects.isNull(value)) {
                    continue;
                }
                setEntryValue(entry, value, format);
                if (CollectionUtils.isNotEmpty(attachFieldList) && attachFieldList.contains(field)) {
                    setImageValue(entry, value);
                }
            }
        }
    }

    private void setImageValue(Map.Entry<String, Object> entry, Object value) {
        File file = this.downloadFile((String) value);
        if (Objects.nonNull(file)) {
            entry.setValue(Pair.of(ImageFile.class, file));
        } else {
            entry.setValue(value);
        }

    }

    private File downloadFile(String fileJson) {
        List<FileDTO> fileDTOList = JSON.parseArray(fileJson, FileDTO.class);
        if (CollectionUtils.isEmpty(fileDTOList)) {
            return null;
        }
        FileDTO fileDTO = fileDTOList.get(0);
        String url = vortexUrlConfig.getFileUrl() + "/vortex/rest/cloud/np/file/downloadFileThumbnail";
        Map<String, Object> paramMap = Maps.newHashMap();
        paramMap.put("id", fileDTO.getId());
        paramMap.put("scale", 0.5);
        ResponseEntity<byte[]> responseEntity = this.restTemplateComponent.postFormResponse(url, paramMap, byte[].class, (HttpHeaders) null);
        if (Objects.isNull(responseEntity.getBody())) {
            return null;
        }
        String msg = new String(responseEntity.getBody());
        if (msg.contains(DOWNLOAD_FILE_THUMBNAIL_ERROR)) {
            log.error("文件下载失败,url:" + url + ",id:" + fileDTO.getId());
            return null;
        } else {
            String name = fileDTO.getName();
            String prefix = StringUtils.substringBeforeLast(name, ".");
            String suffix = StringUtils.substringAfterLast(name, ".");
            File file = null;
            try {
                Path path = Files.createTempFile(prefix, "." + suffix);
                Files.write(path, responseEntity.getBody());
                file = path.toFile();
            } catch (Exception e) {
                log.error("文件下载失败,url:" + url + ",id:" + fileDTO.getId());
            }

            return file;
        }
    }

    private void setEntryValue(Map.Entry<String, Object> entry, Object value, String format) {
        if (value instanceof Date) {
            if (StringUtils.isBlank(format)) {
                format = "yyyy-MM-dd HH:mm:ss";
            }
            String str = DateTimeFormatter.ofPattern(format).format(((Date) value).toInstant());
            entry.setValue(str);
        } else if (value instanceof LocalTime) {
            if (StringUtils.isBlank(format)) {
                format = "HH:mm:ss";
            }
            String str = DateTimeFormatter.ofPattern(format).format((LocalTime) value);
            entry.setValue(str);
        } else if (value instanceof LocalDate) {
            if (StringUtils.isBlank(format)) {
                format = "yyyy-MM-dd";
            }
            String str = DateTimeFormatter.ofPattern(format).format((LocalDate) value);
            entry.setValue(str);
        } else if (value instanceof LocalDateTime) {
            if (StringUtils.isBlank(format)) {
                format = "yyyy-MM-dd HH:mm:ss";
            }
            String str = DateTimeFormatter.ofPattern(format).format((LocalDateTime) value);
            entry.setValue(str);
        } else if (value instanceof DictDataDTO) {
            entry.setValue(((DictDataDTO) value).getValue());
        } else if (value instanceof GeometryDTO) {
            entry.setValue(((GeometryDTO) value).getLngLats());
        }
    }

    public static void selectListNew(Workbook workbook, HashMap<Integer, String[]> downMap, Integer sheetNumber) {
        Sheet sheet = workbook.getSheetAt(sheetNumber);

        if (downMap != null && !downMap.isEmpty()) {
            downMap.forEach((k, v) -> {
                Sheet hidden = workbook.createSheet("hidden" + k);
                //数据源sheet页不显示
                workbook.setSheetHidden(workbook.getSheetIndex(hidden), true);
                CellStyle style = workbook.createCellStyle();
                style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
                style.setAlignment(HorizontalAlignment.CENTER);
                style.setVerticalAlignment(VerticalAlignment.CENTER);
                Row row = null;
                Cell cell = null;
                for (int i = 0; i < v.length; i++) {
                    row = hidden.createRow(i);
                    cell = row.createCell(0);
                    cell.setCellValue(v[i]);
                }
                Name namedCell = workbook.createName();
                namedCell.setNameName("hidden");
                namedCell.setRefersToFormula("hidden!A$1:A$" + v.length);

                DataValidationHelper dvHelper = sheet.getDataValidationHelper();
                DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint("hidden" + k);
                CellRangeAddressList addressList = null;
                HSSFDataValidation validation = null;
                for (int i = 0; i < v.length; i++) {
                    row = sheet.createRow(i);
                    cell = row.createCell(0);
                    cell.setCellStyle(style);
                    addressList = new CellRangeAddressList(1, 65535, k, k);
                    validation = (HSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
                    sheet.addValidationData(validation);
                }
            });
        }

    }

    private Integer getListCount(String code, List<ExportExcelColumnDTO> columns) {
        for (int i = 0; i < columns.size(); i++) {
            if (code.equals(columns.get(i).getField())) {
                return i + 1;
            }
        }
        return null;
    }

    private HashMap<Integer, String[]> getMap(HashMap<String, String[]> downMap, List<ExportExcelColumnDTO> columns) {
        HashMap<Integer, String[]> map = new HashMap<>();
        downMap.forEach((a, b) -> {
            Integer t = getListCount(a, columns);
            if (Objects.nonNull(t)) {
                map.put(t, b);
            }
        });
        return map;
    }

    public void setExcelByCode(Workbook workbook, HashMap<String, String[]> downMap, Integer sheetNumber, List<ExportExcelColumnDTO> columns) {
        Sheet sheet = workbook.getSheetAt(sheetNumber);
        HashMap<Integer, String[]> map = getMap(downMap, columns);
        setExcelCell(workbook, sheetNumber, sheet, map);
    }

    private static void setExcelCell(Workbook workbook, Integer sheetNumber, Sheet sheet, HashMap<Integer, String[]> map) {
        if (map != null && !map.isEmpty()) {
            map.forEach((k, v) -> {
                String hideSheetName = "_" + sheetNumber + "combo_" + k + "_" + k;
                Sheet hideSheet = workbook.createSheet(hideSheetName); // 用于存储 下拉菜单数据
                for (int i = 0; i < v.length; i++) {
                    hideSheet.createRow(i).createCell(0).setCellValue(v[i]);
                }
                Name name = workbook.createName();
                name.setNameName(hideSheetName + "_data");
                name.setRefersToFormula(hideSheetName + "!$A$1:$A$" + v.length);
                DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
                CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 65535, k, k);
                DataValidationConstraint explicitListConstraint = dataValidationHelper.createFormulaListConstraint(hideSheetName + "_data");
                DataValidation validation = dataValidationHelper.createValidation(explicitListConstraint, cellRangeAddressList);
                if (validation instanceof XSSFDataValidation) {
                    validation.setSuppressDropDownArrow(true);
                    validation.setShowErrorBox(true);
                } else {
                    validation.setSuppressDropDownArrow(false);
                }
                sheet.addValidationData(validation);
                workbook.setSheetHidden(workbook.getSheetIndex(hideSheet), true);
            });
        }
    }

    public static void selectList(Workbook workbook, HashMap<Integer, String[]> downMap, Integer sheetNumber) {
        Sheet sheet = workbook.getSheetAt(sheetNumber);
        setExcelCell(workbook, sheetNumber, sheet, downMap);
    }

}
